const express = require('express');
const router = express.Router();
const R = require("../util/json")
const connection = require("../util/db")
const json = require("../util/jsonFormat")
const md5 = require("md5")
const {formatArray,formatObject} = require("../util/jsonFormat");

function extracted(sql,objs,query) {
    const {departmentName,status} = query;
    if (departmentName) {
        sql += 'and department_name like ? ';
        objs.push(`%${departmentName}%`);
    }
    if (status) {
        sql += 'and status = ? ';
        objs.push(status);
    }
    return sql;
}

router.get("/page",function (req, res) {
    const {page=1,limit=5} = req.query;
    const offset = (page-1)*limit;
    let sql = "select * from department where 1 = 1 ";
    const objs = [];
    sql = extracted(sql,objs,req.query);
    sql+='order by id desc limit ?,?';
    objs.push(offset,parseInt(limit));
    connection.query(sql,objs,function (e,r) {
        if(e){
            throw e;
        }
        const dataList = formatArray(r);
        let countSql = "select count(id) count from department where 1 = 1 ";
        const countObjs = [];
        countSql=extracted(countSql,countObjs,req.query);
        connection.query(countSql,countObjs,function (e,r) {
            if(e){
                throw e;
            }
            const count = r[0].count;
            res.json(R.page(count,dataList));
        })
    })
})

router.get("/info/:id",function (req, res) {
    const id = req.params.id;
    connection.query("select * from department where id = ?",[id],function (e,r) {
        if(e){
            throw e;
        }
        if(r.length > 0){
            res.json(R.data(formatObject(r[0])))
        }else{
            res.json(R.error("编号异常"))
        }
    })
})

router.post("/update",function (req, res) {
    const department = req.body;
    if(department.id){
        let sql = "update department set department_name = ?,profile = ?,status = ? where  id = ?";
        const objs=[department.departmentName,department.profile,department.status,department.id];
        connection.query(sql,objs,function (e) {
            if(e){
                throw e;
            }
            res.json(R.ok());
        })
    }else{
        let sql = "insert into department values(null,?,?,?)";
        const objs=[department.departmentName,department.profile,department.status];
        connection.query(sql,objs,function (e) {
            if(e){
                throw e;
            }
            res.json(R.ok());
        })
    }
})

router.delete("/delete/:id",function (req, res) {
    const id = req.params.id;
    connection.query("select * from userinfo where department_id = ?",[id],function (e,r) {
        if(e){
            throw e;
        }
        if(r.length>0){
            res.json(R.error("部门已被使用，禁止删除！"))
        }else{
            connection.query("delete from department where id = ?",[id],function (e) {
                if(e){
                    throw e;
                }
                res.json(R.ok());
            })
        }
    })
})

router.post("/status",function (req,res) {
    const {id,status} = req.body;
    let sql = "update department set status = ? where id = ?";
    const objs = [status,id];
    connection.query(sql,objs,function (e) {
        if(e){
            throw e;
        }
        res.json(R.ok());
    })
})

router.get("/select",function (req, res){
    connection.query("select * from department order by id desc ",[],function (e,r) {
        if(e){
            throw e;
        }
        res.json(R.data(json.formatArray(r)))
    })
})

module.exports = router;
